Rafał Gogłoza

Problem:

Działasz na tabeli opisującej procesy produkcji. Jeden proces może występować więcej niż jeden raz w tabeli. Nas interesuje tylko ostatnia data zakończenia procesu. Użyjemy funkcji okienkowej, row_number, żeby oznaczyć proces, który zakończył się jako ostatni. W jaki sposób w tym samy zapytaniu wybrać ten wiersz, bez używania dodatkowych podzapytań lub CTE?

Rozwiązanie:

Użyjemy Qualify! Qualify w Databricks filtruje wyniki zapytania funkcji okienkowej. Możesz myśleć o nim jak warunku zakładanym na wyniku funkcji okienkowej. Upraszcza to znakomicie składnie.

Jak go zastosować:

select process_id, process_name, process_start_date, process_end_date
 , row_number() over(partition by process_id order by process_end_date desc) as rn
from d_process
qualify rn = 1

Jak zbudować zapytanie z Qualify

Odpytujemy tabelę z procesami. Przechowuje ona historię procesu produkcji części do dronów. Co ciekawe proces produkcji stworzony jest w ten sposób, że w jeden proces dla danego drona może wystąpić więcej niż jeden raz.

Na potrzeby raportowania interesuje nas tylko ostatnie wystąpienie danego procesu.

Zapytania z Qualify można zrobić w dwa sposoby.

Umieszczając funkcję okienkową w części select a qualify za from i listą tabel

select process_id, process_name, process_start_date, process_end_date
 , row_number() over(partition by process_id order by process_end_date desc) as rn
from d_process
qualify rn = 1

Można też, przenieść funkcję okienkową do klauzuli Qualify:

select process_id, process_name, process_start_date, process_end_date
from d_process
qualify row_number() over(partition by process_id order by process_end_date desc) = 1

Dla mnie pierwszy zapis jest bardziej czytelny.

Jaki będzie równoważny kod?

Równoważny kod, bez użycia Qualify można zrobić na przykład stosując CTE.

with process_selection as (
    select process_id, process_name, process_start_date, process_end_date
     , row_number() over(partition by process_id order by process_end_date desc) as rn
    from d_process
)
select * from process_selection
where rn = 1

Zapis wymaga CTE i kolejnego zapytania, żeby wybrać wartości.

Czy ten zapis może jest czytelny albo jesteś do niego przyzwyczajony ale czy wykonanie zapytania będzie bardziej wydajne?

Jak sprawdzić wydajność

W celu sprawdzenia wydajności napisałem dwa zapytania: wykorzystujące składnie Qualify oraz CTE z kolejnym zapytaniem.

Uruchamiałem kod na zbiorze danych przekraczającym 75 milionów rekordów. Uruchomienie odbywało się na klustrze w podstawowej konfiguracji, bez włączonej optymalizacji oraz bez włączonego Photona. Kluster miałem całkowicie do swojej dyspozycji i nic innego nie było uruchomione na klustrze - idealne warunki do sprawdzania wydajności.

Uruchamiałem zapytania kilka razy za każdym razy materializując wyniki do tabeli, tabele tworzone przez te zapytania miały taką samą strukturę i tyle samo wierszy.

Wyniki testów wydajności mnie zaskoczyły.

Oczekiwałem, że Qualify okaże się zdecydowanie szybszy. Niestety w większości wypadków był wolniejszy o kilka sekund (1-5 żeby być dokładnym) niż użycie CTE.

Po pierwszych testach wykonałem kolejną serię, ale wcześniej przeprowadziłem jeszcze "porządkowanie" danych w tabeli.

Uruchomiłem:

optimize d_process;
analyze table d_process compute statistics for all columns;

Tym razem lepszą wydajność pokazał Qualify też o kilka sekund (2-7). Całościowy czas wykonania oscylował przedziale od 46 do 56 sekund i te klika sekund różnicy jest niezauważalne.

Qualify w Databricks - rekomendacje

Możesz zadać pytanie: to jak w końcu, używać tego Qualify czy nie? Jak zwykle odpowiedź nie jest jednoznaczna i można odpowiedzieć: To zależy.

Jakie są standardy w Twoim zespole albo narzucone standardy organizacyjne? Jak się umówiliście, że będziecie tworzyć kod? Używając Qualify czy nie? Macie standardy czy nie macie standardów? Czy może pracujecie nad stworzeniem standardów?

Czy świta Ci z tyłu głowy myśl, że Databricks może nie być docelową platformą i może kiedyś przyjdzie potrzeba przeniesienia kodu na inną platformę? Wtedy może lepiej trzymać się standardu SQL i nie używać Qualify, które nie należy do standardu ansi-SQL.

Czy jeżeli mam napisany kod i używasz tam CTE i row_number, teraz będziesz zmieniał go na Qualify? Czy tylko w nowych notebookach będziesz stosował Qualify?

Dla mnie Qualify znacznie upraszcza składnie. Nie ma potrzeby wykorzystywana CTE i można zawrzeć wszystko w jednym zapytaniu. Więc będę stosował dla nowych zmian. Istniejący kod, wykorzystujący CTE i row_number, będę zmieniał tylko wtedy gdy będę wprowadzał modyfikacje do istniejącego notebooka.

https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-qualify